using OrderSystem.SystemFramework;
using System;
using System.Data;
using System.Data.SqlClient;

namespace OrderSystem.DataAccess
{
	public class Projects : IDisposable
	{
		private SqlDataAdapter dataAdapter;

		private SqlCommand selectCommand;

		private SqlCommand insertCommand;

		private SqlCommand updateCommand;

		private SqlCommand deleteCommand;

		public DataTable GetProjects(string strQuery)
		{
			DataTable dataTable = new DataTable("Project");
			try
			{
				this.selectCommand = new SqlCommand(strQuery, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public DataTable LoadProject(int projectID)
		{
			DataTable dataTable = new DataTable("Project");
			string cmdText = "SELECT pj.PKID AS 序号,pj.CustomerID AS 客户ID,c.CompanyName AS 客户,pj.ProjectName AS 计划,pj.Linkman AS 联系人,pj.LinkmanPhone AS 联系电话,pj.ProjectAddress AS 地址,pj.Remark AS 备注 FROM Project AS pj INNER JOIN Customer AS c ON pj.CustomerID=c.PKID WHERE pj.PKID=@ProjectID";
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@ProjectID", SqlDbType.Int));
				this.selectCommand.Parameters["@ProjectID"].Value = projectID;
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public bool InsertProject(int customerID, string projectName, string linkman, string phone, string projectAddress, string remark)
		{
			try
			{
				this.insertCommand = new SqlCommand("INSERT INTO Project VALUES(@CustomerID,@ProjectName,@Linkman,@Phone,@ProjectAddress,@Locked,@Remark)", new SqlConnection(AppSettings.ConnectionString));
				this.insertCommand.Connection.Open();
				this.insertCommand.Parameters.Add(new SqlParameter("@CustomerID", SqlDbType.Int));
				this.insertCommand.Parameters["@CustomerID"].Value = customerID;
				this.insertCommand.Parameters.Add(new SqlParameter("@ProjectName", SqlDbType.NVarChar, 32));
				this.insertCommand.Parameters["@ProjectName"].Value = projectName;
				this.insertCommand.Parameters.Add(new SqlParameter("@Linkman", SqlDbType.NVarChar, 16));
				this.insertCommand.Parameters["@Linkman"].Value = linkman;
				this.insertCommand.Parameters.Add(new SqlParameter("@Phone", SqlDbType.NVarChar, 32));
				this.insertCommand.Parameters["@Phone"].Value = phone;
				this.insertCommand.Parameters.Add(new SqlParameter("@ProjectAddress", SqlDbType.NVarChar, 64));
				this.insertCommand.Parameters["@ProjectAddress"].Value = projectAddress;
				this.insertCommand.Parameters.Add(new SqlParameter("@Locked", SqlDbType.Bit));
				this.insertCommand.Parameters["@Locked"].Value = false;
				this.insertCommand.Parameters.Add(new SqlParameter("@Remark", SqlDbType.NVarChar, 64));
				this.insertCommand.Parameters["@Remark"].Value = remark;
				this.insertCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool UpdateProject(int pkid, int customerID, string projectName, string linkman, string phone, string projectAddress, string remark)
		{
			try
			{
				this.updateCommand = new SqlCommand("UPDATE Project SET CustomerID=@CustomerID,ProjectName=@ProjectName,Linkman=@Linkman,LinkmanPhone=@Phone,ProjectAddress=@ProjectAddress,Remark=@Remark WHERE PKID=@PKID", new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@PKID", SqlDbType.Int));
				this.updateCommand.Parameters["@PKID"].Value = pkid;
				this.updateCommand.Parameters.Add(new SqlParameter("@CustomerID", SqlDbType.Int));
				this.updateCommand.Parameters["@CustomerID"].Value = customerID;
				this.updateCommand.Parameters.Add(new SqlParameter("@ProjectName", SqlDbType.NVarChar, 32));
				this.updateCommand.Parameters["@ProjectName"].Value = projectName;
				this.updateCommand.Parameters.Add(new SqlParameter("@Linkman", SqlDbType.NVarChar, 16));
				this.updateCommand.Parameters["@Linkman"].Value = linkman;
				this.updateCommand.Parameters.Add(new SqlParameter("@Phone", SqlDbType.NVarChar, 32));
				this.updateCommand.Parameters["@Phone"].Value = phone;
				this.updateCommand.Parameters.Add(new SqlParameter("@ProjectAddress", SqlDbType.NVarChar, 64));
				this.updateCommand.Parameters["@ProjectAddress"].Value = projectAddress;
				this.updateCommand.Parameters.Add(new SqlParameter("@Remark", SqlDbType.NVarChar, 64));
				this.updateCommand.Parameters["@Remark"].Value = remark;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool Delete(int nProjectID)
		{
			try
			{
				this.deleteCommand = new SqlCommand("Delete from Project where PKID=@ProjectID", new SqlConnection(AppSettings.ConnectionString));
				this.deleteCommand.Connection.Open();
				this.deleteCommand.Parameters.Add(new SqlParameter("@ProjectID", SqlDbType.Int));
				this.deleteCommand.Parameters["@ProjectID"].Value = nProjectID;
				this.deleteCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public void Dispose()
		{
			this.Dispose(true);
			GC.SuppressFinalize(true);
		}

		protected virtual void Dispose(bool disposing)
		{
			if (disposing)
			{
				if (this.dataAdapter != null)
				{
					if (this.dataAdapter.SelectCommand != null)
					{
						if (this.dataAdapter.SelectCommand.Connection != null)
						{
							this.dataAdapter.SelectCommand.Connection.Dispose();
							this.dataAdapter.SelectCommand.Connection = null;
						}
						this.dataAdapter.SelectCommand.Dispose();
						this.dataAdapter.SelectCommand = null;
					}
					this.dataAdapter.Dispose();
					this.dataAdapter = null;
				}
				if (this.insertCommand != null)
				{
					if (this.insertCommand.Connection != null)
					{
						this.insertCommand.Connection.Dispose();
						this.insertCommand.Connection = null;
					}
					this.insertCommand.Dispose();
					this.insertCommand = null;
				}
				if (this.updateCommand != null)
				{
					if (this.updateCommand.Connection != null)
					{
						this.updateCommand.Connection.Dispose();
						this.updateCommand.Connection = null;
					}
					this.updateCommand.Dispose();
					this.updateCommand = null;
				}
				if (this.deleteCommand != null)
				{
					if (this.deleteCommand.Connection != null)
					{
						this.deleteCommand.Connection.Dispose();
						this.deleteCommand.Connection = null;
					}
					this.deleteCommand.Dispose();
					this.deleteCommand = null;
				}
			}
		}
	}
}
